from datetime import datetime
from string import Template
from tack.lib import string_app, json_app, content_headers, status_codes
from util import build_row_dict, build_row_dict_for_sql, fill_dict_from_form, build_columns_values_sql, state_def


class Sql:
    select_details_by_commitment_id = """
    SELECT tcd.commitment_id,
           tcd.commitment_detail_id,
           tcd.state_start,
           tcd.state_finish,
           tcd.state_key,
           tcd.requested_by,
           tcd.requester_phone,
           tcd.trans_coord_name,
           tcd.trans_coord_phone,
           tcd.org_req_name,
           tcd.org_req_phone,
           tcd.purpose,
           tcd.poc_name,
           tcd.poc_phone,
           tcd.poc_alt_phone,
           tcd.poc_smtp,
           tcd.deliver_post,
           tcd.wait,
           tcd.fund_cite
    FROM tmpo_commitment_detail tcd
    WHERE tcd.commitment_id = $commitment_id
        AND tcd.state_finish IS NULL
    """


    set_details_finish = """
    UPDATE tmpo_commitment_detail
    SET state_finish = '$time_stamp'
    WHERE commitment_detail_id = $commitment_detail_id_finish
    """


    insert_commitment_details = """
    INSERT INTO tmpo_commitment_detail
    """


    select_driver_commitments_by_date = """
    SELECT DISTINCT tcd.commitment_id AS commitment_id
    FROM tmpo_commitment tc
        LEFT OUTER JOIN tmpo_commitment_detail tcd
            ON tc.commitment_id = tcd.commitment_id
        LEFT OUTER JOIN tmpo_commitment_leg tcl
            ON tc.commitment_id = tcl.commitment_id
        INNER JOIN tmpo_commitment_leg_driver tcld
            ON tcl.commitment_leg_id = tcld.commitment_leg_id
    WHERE tcld.driver_id = $driver_id
        AND tcd.state_key = 3
        AND tcd.state_finish IS NULL
        AND NOT (tcl.finish < $start)
        AND NOT (tcl.start > $finish)
    """


    select_vehicle_commitments_by_date = """
    SELECT DISTINCT tcd.commitment_id AS commitment_id
    FROM tmpo_commitment tc
        LEFT OUTER JOIN tmpo_commitment_detail tcd
            ON tc.commitment_id = tcd.commitment_id
        LEFT OUTER JOIN tmpo_commitment_leg tcl
            ON tc.commitment_id = tcl.commitment_id
        INNER JOIN tmpo_commitment_leg_vehicle tclv
            ON tcl.commitment_leg_id = tclv.commitment_leg_id
    WHERE tclv.vehicle_id = $vehicle_id
        AND tcd.state_key = 3
        AND tcd.state_finish IS NULL
        AND NOT (tcl.finish < $start)
        AND NOT (tcl.start > $finish)
    """


    select_driver_legs_by_date = """
    SELECT DISTINCT tcl.commitment_leg_id AS commitment_leg_id
    FROM tmpo_commitment tc
        LEFT OUTER JOIN tmpo_commitment_detail tcd
            ON tc.commitment_id = tcd.commitment_id
        LEFT OUTER JOIN tmpo_commitment_leg tcl
            ON tc.commitment_id = tcl.commitment_id
        INNER JOIN tmpo_commitment_leg_driver tcld
            ON tcl.commitment_leg_id = tcld.commitment_leg_id
    WHERE tcld.driver_id = $driver_id
        AND tcd.state_key = 3
        AND tcd.state_finish IS NULL
        AND NOT (tcl.finish < $start)
        AND NOT (tcl.start > $finish)
    """


    select_vehicle_legs_by_date = """
    SELECT DISTINCT tcl.commitment_leg_id AS commitment_leg_id
    FROM tmpo_commitment tc
        LEFT OUTER JOIN tmpo_commitment_detail tcd
            ON tc.commitment_id = tcd.commitment_id
        LEFT OUTER JOIN tmpo_commitment_leg tcl
            ON tc.commitment_id = tcl.commitment_id
        INNER JOIN tmpo_commitment_leg_vehicle tclv
            ON tcl.commitment_leg_id = tclv.commitment_leg_id
    WHERE tclv.vehicle_id = $vehicle_id
        AND tcd.state_key = 3
        AND tcd.state_finish IS NULL
        AND NOT (tcl.finish < $start)
        AND NOT (tcl.start > $finish)
    """


    remove_driver_by_leg = """
    DELETE FROM tmpo_commitment_leg_driver
    WHERE driver_id = $driver_id
        AND commitment_leg_id = $commitment_leg_id
    """


    remove_vehicle_by_leg = """
    DELETE FROM tmpo_commitment_leg_vehicle
    WHERE vehicle_id = $vehicle_id
        AND commitment_leg_id = $commitment_leg_id
    """


def set_to_conflicted_using_driver(environ, d, s, f):
    driver_id = d
    start = s
    finish = f
    cursor = environ['connection'].cursor()
    sql = Sql.select_driver_commitments_by_date
    sql = Template(sql).substitute(locals())
    cursor.execute(sql)
    comms = cursor.fetchall()
    comms_cd = cursor.description
    sql = Sql.select_driver_legs_by_date
    sql = Template(sql).substitute(locals())
    cursor.execute(sql)
    legs = cursor.fetchall()
    legs_cd = cursor.description
    for leg in legs:
        l = build_row_dict(legs_cd, leg)
        commitment_leg_id = l['commitment_leg_id']
        sql = Sql.remove_driver_by_leg
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        environ['connection'].commit()
    for comm in comms:
        c = build_row_dict(comms_cd, comm)
        commitment_id = c['commitment_id']
        sql = Sql.select_details_by_commitment_id
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        rows = cursor.fetchall()
        rows_cd = cursor.description
        if rows:
            throwaway = datetime.now()
            time_stamp = throwaway.strftime('%Y-%m-%d %H:%M:%S')
            state_dict = build_row_dict_for_sql(rows_cd, rows[0])
            commitment_detail_id_finish = state_dict['commitment_detail_id']
            state_dict['state_start'] = "'" + time_stamp + "'"
            del state_dict['state_finish'], state_dict['commitment_detail_id']
            state_dict['state_key'] = state_def.CONFLICTED
            sql = Sql.insert_commitment_details
            sql = sql + build_columns_values_sql(state_dict)
            cursor.execute(sql)
            environ['connection'].commit()
            sql = Sql.set_details_finish
            sql = Template(sql).substitute(locals())
            cursor.execute(sql)
            environ['connection'].commit()


def set_to_conflicted_using_vehicle(environ, v, s, f):
    vehicle_id = v
    start = s
    finish = f
    cursor = environ['connection'].cursor()
    sql = Sql.select_vehicle_commitments_by_date
    sql = Template(sql).substitute(locals())
    cursor.execute(sql)
    comms = cursor.fetchall()
    comms_cd = cursor.description
    sql = Sql.select_vehicle_legs_by_date
    sql = Template(sql).substitute(locals())
    cursor.execute(sql)
    legs = cursor.fetchall()
    legs_cd = cursor.description
    for leg in legs:
        l = build_row_dict(legs_cd, leg)
        commitment_leg_id = l['commitment_leg_id']
        sql = Sql.remove_vehicle_by_leg
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        environ['connection'].commit()
    for comm in comms:
        c = build_row_dict(comms_cd, comm)
        commitment_id = c['commitment_id']
        sql = Sql.select_details_by_commitment_id
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        rows = cursor.fetchall()
        rows_cd = cursor.description
        if rows:
            throwaway = datetime.now()
            time_stamp = throwaway.strftime('%Y-%m-%d %H:%M:%S')
            state_dict = build_row_dict_for_sql(rows_cd, rows[0])
            commitment_detail_id_finish = state_dict['commitment_detail_id']
            state_dict['state_start'] = "'" + time_stamp + "'"
            del state_dict['state_finish'], state_dict['commitment_detail_id']
            state_dict['state_key'] = state_def.CONFLICTED
            sql = Sql.insert_commitment_details
            sql = sql + build_columns_values_sql(state_dict)
            cursor.execute(sql)
            environ['connection'].commit()
            sql = Sql.set_details_finish
            sql = Template(sql).substitute(locals())
            cursor.execute(sql)
            environ['connection'].commit()

